# Define your item pipelines here
#
# Don't forget to add your pipeline to the ITEM_PIPELINES setting
# See: https://docs.scrapy.org/en/latest/topics/item-pipeline.html


# useful for handling different item types with a single interface
from itemadapter import ItemAdapter
import os
import pymysql

import logging
from FetchFund import settings
import time


class MysqlPipeline:

    def __init__(self):
        self.connect = pymysql.connect(
            host=settings.MYSQL_HOST,
            db=settings.MYSQL_DBNAME,
            user=settings.MYSQL_USER,
            passwd=settings.MYSQL_PASSWD,
            port=settings.MYSQL_PORT,
            charset='utf8',
            use_unicode=True)
        self.cursor = self.connect.cursor()
        self.cursor.execute("""DROP TABLE IF EXISTS t_fund,t_detail""")
        self.cursor.execute("""create table t_fund
            (
                id                 int auto_increment      primary key,
                fund_code          varchar(16)  null comment '基金代码',
                fund_name          varchar(64)  null comment '基金简称',
                fund_link          varchar(255) null comment '基金详细页面链接',
                fund_current_value varchar(64)  null comment '当前单位净值',
                fund_current_date  varchar(32)  null comment '当前日期',
                fund_total_value   varchar(32)  null comment '累计净值',
                one_month_rate     varchar(16)  null comment '近一月涨跌',
                three_month_rate   varchar(16)  null comment '近三月涨跌',
                six_month_rate     varchar(16)  null comment '近六月涨跌',
                one_year_rate      varchar(16)  null comment '近一年涨跌',
                three_year_rate    varchar(16)  null comment '近三年涨跌',
                total_rate         varchar(16)  null comment '成立以来涨跌',
                fund_start_time    varchar(32)  null comment '成立时间',
                fund_manager       varchar(32)  null comment '基金经理',
                fund_size          varchar(32)  null comment '基金规模',
                fund_type          varchar(32)  null comment '基金类型',
                fund_level        varchar(32)  null comment '基金评级'
            )""")

        # self.cursor.execute("""create table t_detail
        #     (
        #         id        int auto_increment   primary key,
        #         fund_code varchar(16) null comment '基金编号',
        #         nv_date      varchar(32) null comment '净值日期',
        #         na_value      varchar(32) null comment '单位净值',
        #         cna_value      varchar(32) null comment '累积净值'
        #     )""")
        self.connect.commit()

        self.data=[]


    # pipeline默认调用
    def process_item(self, item, spider):
        self.data.append((item['fund_code'],item['fund_name'],item['fund_link'],item['fund_current_value'],item['fund_current_date'],item['fund_total_value'],
                 item['one_month_rate'],item['three_month_rate'],item['six_month_rate'],item['one_year_rate'],item['three_year_rate'],item['total_rate'],
                 item['fund_start_time'],item['fund_manager'],item['fund_size'],item['fund_type'],item['fund_level']))
        if len(self.data)>300:
            self._write2db()
        return item

    def close_spider(self, spider):
        if len(self.data) > 0:
            self._write2db()
        self.cursor.close()
        self.connect.close()
        return

    def _write2db(self):
        try:
            self.cursor.executemany(
                """insert into t_fund(fund_code,fund_name,fund_link,fund_current_value,fund_current_date,fund_total_value,
                  one_month_rate,three_month_rate,six_month_rate,one_year_rate,three_year_rate,total_rate,
                  fund_start_time,fund_manager,fund_size,fund_type,fund_level)
                  value (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)""",
                self.data)

            # 历史记录暂时不用
            # for detail in item['history_value']:
            #     self.cursor.execute(
            #         """insert into t_detail(fund_code,fsrq,dwjz,ljjz)
            #           value (%s,%s,%s,%s)""",
            #         (detail['fund_code'], detail['nv_date'], detail['na_value'], detail['cna_value']))

            self.data.clear()
            self.connect.commit()
        except Exception as error:
            logging.exception(error)



# alchemy orm
class MysqlAlchemyPipeline:
    def __init__(self):
        pass